"""
记账相关的异步 CRUD 操作
"""

from datetime import date, datetime
from decimal import Decimal
from typing import Any

from sqlalchemy import or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from src.models.accounting_model import (
    Account,
    AccountType,
    Category,
    Project,
    Transaction,
    TransactionType,
)
from src.models.family_model import Family, FamilyMember


# 分类相关的 CRUD 操作
async def get_category_by_id(
    db: AsyncSession, category_id: int, family_id: int
) -> Category | None:
    """根据 ID 获取分类"""
    result = await db.execute(
        select(Category).where(
            Category.id == category_id, Category.family_id == family_id
        )
    )
    category = result.scalar_one_or_none()

    # 获取家庭名称和父分类名称
    if category:
        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        category.family_name = family.name if family else None

        # 获取父分类名称
        if category.parent_id:
            parent_result = await db.execute(
                select(Category).where(
                    Category.id == category.parent_id, Category.family_id == family_id
                )
            )
            parent = parent_result.scalar_one_or_none()
            category.parent_name = parent.name if parent else None

    return category


async def get_categories(
    db: AsyncSession,
    family_id: int,
    parent_id: int | None = None,
    skip: int = 0,
    limit: int = 100,
) -> list[Category]:
    """获取分类列表"""
    query = select(Category).where(Category.family_id == family_id)
    if parent_id is not None:
        query = query.where(Category.parent_id == parent_id)
    else:
        # 获取顶级分类
        query = query.where(Category.parent_id.is_(None))

    query = query.order_by(Category.sort_order).offset(skip).limit(limit)
    result = await db.execute(query)
    categories = list(result.scalars().all())

    # 获取家庭名称和父分类名称
    if categories:
        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        # 获取所有父分类
        parent_ids = list(set(c.parent_id for c in categories if c.parent_id))
        parent_names = {}
        if parent_ids:
            parent_query = select(Category).where(
                Category.id.in_(parent_ids), Category.family_id == family_id
            )
            parent_result = await db.execute(parent_query)
            parent_names = {p.id: p.name for p in parent_result.scalars().all()}

        # 添加名称到分类
        for category in categories:
            category.family_name = family_name
            category.parent_name = parent_names.get(category.parent_id)

    return categories


async def get_all_categories(
    db: AsyncSession, family_id: int, skip: int = 0, limit: int = 100
) -> list[Category]:
    """获取所有分类(包括子分类)"""
    query = (
        select(Category)
        .where(Category.family_id == family_id)
        .order_by(Category.sort_order)
        .offset(skip)
        .limit(limit)
    )
    result = await db.execute(query)
    return list(result.scalars().all())


async def create_category(
    db: AsyncSession,
    name: str,
    family_id: int,
    parent_id: int | None = None,
    **kwargs,
) -> Category:
    """创建分类"""
    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    # 获取父分类名称
    parent_name = None
    if parent_id:
        parent_result = await db.execute(
            select(Category).where(
                Category.id == parent_id, Category.family_id == family_id
            )
        )
        parent = parent_result.scalar_one_or_none()
        parent_name = parent.name if parent else None

    db_category = Category(
        name=name,
        family_id=family_id,
        family_name=family_name,
        parent_id=parent_id,
        parent_name=parent_name,
        **kwargs,
    )
    db.add(db_category)
    await db.commit()
    await db.refresh(db_category)

    return db_category


async def update_category(
    db: AsyncSession, category_id: int, family_id: int, **kwargs
) -> Category | None:
    """更新分类"""
    category = await get_category_by_id(db, category_id, family_id)
    if not category:
        return None

    for key, value in kwargs.items():
        if hasattr(category, key) and value is not None:
            setattr(category, key, value)

    await db.commit()
    await db.refresh(category)
    return category


async def delete_category(db: AsyncSession, category_id: int, family_id: int) -> bool:
    """删除分类"""
    category = await get_category_by_id(db, category_id, family_id)
    if not category:
        return False

    # 检查是否有子分类
    result = await db.execute(select(Category).where(Category.parent_id == category_id))
    children = result.scalars().all()
    if children:
        return False

    # 检查是否有关联的交易记录
    transaction_result = await db.execute(
        select(Transaction).where(
            Transaction.category_id == category_id,
            Transaction.family_id == family_id,
            Transaction.is_deleted == False,
        )
    )
    transactions = transaction_result.scalars().all()
    if transactions:
        return False

    await db.delete(category)
    await db.commit()
    return True


# 账户相关的 CRUD 操作
async def get_account_by_id(
    db: AsyncSession, account_id: int, family_id: int, include_inactive: bool = False
) -> Account | None:
    """根据 ID 获取账户"""
    query = select(Account).where(
        Account.id == account_id, Account.family_id == family_id
    )
    if not include_inactive:
        query = query.where(Account.is_active)

    result = await db.execute(query)
    account = result.scalar_one_or_none()

    # 获取家庭名称
    if account:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        account.family_name = family.name if family else None

    return account


async def get_accounts(
    db: AsyncSession,
    family_id: int,
    skip: int = 0,
    limit: int = 100,
    is_active: bool | None = None,
    account_type: str | None = None,
) -> list[Account]:
    """获取账户列表"""
    query = select(Account).where(Account.family_id == family_id)
    if is_active is not None:
        query = query.where(Account.is_active == is_active)
    else:
        # 默认只返回活跃的账户
        query = query.where(Account.is_active)

    if account_type is not None:
        # 如果传入的是字符串,尝试转换为AccountType枚举
        try:
            account_type_enum = AccountType(account_type)
            query = query.where(Account.account_type == account_type_enum)
        except ValueError:
            # 如果转换失败,直接使用字符串比较
            query = query.where(Account.account_type == account_type)

    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    accounts = list(result.scalars().all())

    # 获取家庭名称并添加到每个账户
    if accounts:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        for account in accounts:
            account.family_name = family_name

    return accounts


async def create_account(
    db: AsyncSession,
    name: str,
    account_type: AccountType,
    family_id: int,
    initial_balance: Decimal = Decimal("0.00"),
    **kwargs,
) -> Account:
    """创建账户"""
    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    db_account = Account(
        name=name,
        account_type=account_type,
        family_id=family_id,
        family_name=family_name,
        initial_balance=initial_balance,
        balance=initial_balance,
        **kwargs,
    )
    db.add(db_account)
    await db.commit()
    await db.refresh(db_account)

    return db_account


async def update_account(
    db: AsyncSession, account_id: int, family_id: int, **kwargs
) -> Account | None:
    """更新账户"""
    account = await get_account_by_id(db, account_id, family_id, include_inactive=True)
    if not account:
        return None

    for key, value in kwargs.items():
        if hasattr(account, key) and value is not None:
            setattr(account, key, value)

    await db.commit()
    await db.refresh(account)
    return account


async def delete_account(db: AsyncSession, account_id: int, family_id: int) -> bool:
    """删除账户"""
    account = await get_account_by_id(db, account_id, family_id)
    if not account:
        return False

    # 检查是否有关联的交易记录
    transaction_result = await db.execute(
        select(Transaction).where(
            or_(
                Transaction.account_id == account_id,
                Transaction.to_account_id == account_id,
            ),
            Transaction.family_id == family_id,
            Transaction.is_deleted == False,
        )
    )
    transactions = transaction_result.scalars().all()
    if transactions:
        return False

    # 软删除
    account.is_active = False
    await db.commit()
    return True


async def update_account_balance(
    db: AsyncSession,
    account_id: int,
    family_id: int,
    amount: Decimal,
    is_increase: bool = True,
) -> Account | None:
    """更新账户余额"""
    account = await get_account_by_id(db, account_id, family_id)
    if not account:
        return None

    if is_increase:
        account.balance += amount
    else:
        account.balance -= amount

    await db.commit()
    await db.refresh(account)
    return account


# 项目相关的 CRUD 操作
async def get_project_by_id(
    db: AsyncSession, project_id: int, family_id: int
) -> Project | None:
    """根据 ID 获取项目"""
    result = await db.execute(
        select(Project).where(
            Project.id == project_id,
            Project.family_id == family_id,
            Project.is_active,
        )
    )
    project = result.scalar_one_or_none()

    # 获取家庭名称
    if project:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        project.family_name = family.name if family else None

    return project


async def get_projects(
    db: AsyncSession,
    family_id: int,
    skip: int = 0,
    limit: int = 100,
    is_active: bool | None = None,
) -> list[Project]:
    """获取项目列表"""
    query = select(Project).where(Project.family_id == family_id)
    if is_active is not None:
        query = query.where(Project.is_active == is_active)

    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    projects = list(result.scalars().all())

    # 获取家庭名称并添加到每个项目
    if projects:
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        for project in projects:
            project.family_name = family_name

    return projects


async def create_project(
    db: AsyncSession, name: str, family_id: int, **kwargs
) -> Project:
    """创建项目"""
    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    db_project = Project(
        name=name, family_id=family_id, family_name=family_name, **kwargs
    )
    db.add(db_project)
    await db.commit()
    await db.refresh(db_project)

    return db_project


async def update_project(
    db: AsyncSession, project_id: int, family_id: int, **kwargs
) -> Project | None:
    """更新项目"""
    project = await get_project_by_id(db, project_id, family_id)
    if not project:
        return None

    for key, value in kwargs.items():
        if hasattr(project, key) and value is not None:
            setattr(project, key, value)

    await db.commit()
    await db.refresh(project)
    return project


async def delete_project(db: AsyncSession, project_id: int, family_id: int) -> bool:
    """删除项目"""
    project = await get_project_by_id(db, project_id, family_id)
    if not project:
        return False

    # 检查是否有关联的交易记录
    transaction_result = await db.execute(
        select(Transaction).where(
            Transaction.project_id == project_id,
            Transaction.family_id == family_id,
            Transaction.is_deleted == False,
        )
    )
    transactions = transaction_result.scalars().all()
    if transactions:
        return False

    # 软删除
    project.is_active = False
    await db.commit()
    return True


# 交易记录相关的 CRUD 操作
async def get_transaction_by_id(
    db: AsyncSession, transaction_id: int, family_id: int
) -> Transaction | None:
    """根据 ID 获取交易记录"""
    result = await db.execute(
        select(Transaction).where(
            Transaction.id == transaction_id,
            Transaction.family_id == family_id,
            Transaction.is_deleted == False,
        )
    )
    transaction = result.scalar_one_or_none()

    # 获取所有相关名称
    if transaction:
        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        transaction.family_name = family.name if family else None

        # 获取分类名称
        if transaction.category_id:
            category_result = await db.execute(
                select(Category).where(
                    Category.id == transaction.category_id,
                    Category.family_id == family_id,
                )
            )
            category = category_result.scalar_one_or_none()
            transaction.category_name = category.name if category else None

        # 获取账户名称
        account_result = await db.execute(
            select(Account).where(
                Account.id == transaction.account_id, Account.family_id == family_id
            )
        )
        account = account_result.scalar_one_or_none()
        transaction.account_name = account.name if account else None

        # 获取目标账户名称
        if transaction.to_account_id:
            to_account_result = await db.execute(
                select(Account).where(
                    Account.id == transaction.to_account_id,
                    Account.family_id == family_id,
                )
            )
            to_account = to_account_result.scalar_one_or_none()
            transaction.to_account_name = to_account.name if to_account else None

        # 获取项目名称
        if transaction.project_id:
            project_result = await db.execute(
                select(Project).where(
                    Project.id == transaction.project_id, Project.family_id == family_id
                )
            )
            project = project_result.scalar_one_or_none()
            transaction.project_name = project.name if project else None

        # 获取成员名称
        member_result = await db.execute(
            select(FamilyMember).where(
                FamilyMember.id == transaction.member_id,
                FamilyMember.family_id == family_id,
            )
        )
        member = member_result.scalar_one_or_none()
        transaction.member_name = member.full_name if member else None

    return transaction


async def get_transactions(
    db: AsyncSession,
    family_id: int,
    skip: int = 0,
    limit: int = 100,
    transaction_type: TransactionType | None = None,
    account_id: int | None = None,
    category_id: int | None = None,
    project_id: int | None = None,
    member_id: int | None = None,
    start_date: date | None = None,
    end_date: date | None = None,
    search: str | None = None,
) -> list[Transaction]:
    """获取交易记录列表"""
    query = select(Transaction).where(
        Transaction.family_id == family_id, Transaction.is_deleted == False
    )

    # 添加过滤条件
    if transaction_type:
        query = query.where(Transaction.transaction_type == transaction_type)
    if account_id:
        query = query.where(
            or_(
                Transaction.account_id == account_id,
                Transaction.to_account_id == account_id,
            )
        )
    if category_id:
        query = query.where(Transaction.category_id == category_id)
    if project_id:
        query = query.where(Transaction.project_id == project_id)
    if member_id:
        query = query.where(Transaction.member_id == member_id)
    if start_date:
        query = query.where(Transaction.transaction_date >= start_date)
    if end_date:
        # Include the entire end_date by setting it to the end of the day
        end_datetime = datetime.combine(end_date, datetime.max.time())
        query = query.where(Transaction.transaction_date <= end_datetime)
    if search:
        query = query.where(
            or_(
                Transaction.description.ilike(f"%{search}%"),
                Transaction.tags.ilike(f"%{search}%"),
            )
        )

    query = (
        query.order_by(Transaction.transaction_date.desc()).offset(skip).limit(limit)
    )
    result = await db.execute(query)
    transactions = list(result.scalars().all())

    # 获取所有相关名称
    if transactions:
        # 获取家庭名称
        family_result = await db.execute(select(Family).where(Family.id == family_id))
        family = family_result.scalar_one_or_none()
        family_name = family.name if family else None

        # 获取所有相关ID
        category_ids = list(set(t.category_id for t in transactions if t.category_id))
        account_ids = list(
            set(t.account_id for t in transactions)
            | set(t.to_account_id for t in transactions if t.to_account_id)
        )
        project_ids = list(set(t.project_id for t in transactions if t.project_id))
        member_ids = list(set(t.member_id for t in transactions))

        # 批量获取相关实体
        categories = {}
        accounts = {}
        projects = {}
        members = {}

        if category_ids:
            category_query = select(Category).where(
                Category.id.in_(category_ids), Category.family_id == family_id
            )
            category_result = await db.execute(category_query)
            categories = {c.id: c.name for c in category_result.scalars().all()}

        if account_ids:
            account_query = select(Account).where(
                Account.id.in_(account_ids), Account.family_id == family_id
            )
            account_result = await db.execute(account_query)
            accounts = {a.id: a.name for a in account_result.scalars().all()}

        if project_ids:
            project_query = select(Project).where(
                Project.id.in_(project_ids), Project.family_id == family_id
            )
            project_result = await db.execute(project_query)
            projects = {p.id: p.name for p in project_result.scalars().all()}

        if member_ids:
            member_query = select(FamilyMember).where(
                FamilyMember.id.in_(member_ids), FamilyMember.family_id == family_id
            )
            member_result = await db.execute(member_query)
            members = {m.id: m.full_name for m in member_result.scalars().all()}

        # 添加名称到交易记录
        for transaction in transactions:
            transaction.family_name = family_name
            transaction.category_name = categories.get(transaction.category_id)
            transaction.account_name = accounts.get(transaction.account_id)
            transaction.to_account_name = accounts.get(transaction.to_account_id)
            transaction.project_name = projects.get(transaction.project_id)
            transaction.member_name = members.get(transaction.member_id)

    return transactions


async def create_transaction(
    db: AsyncSession,
    transaction_type: TransactionType,
    amount: Decimal,
    account_id: int,
    member_id: int,
    family_id: int,
    transaction_date: datetime,
    **kwargs,
) -> Transaction:
    """创建交易记录"""
    # 获取所有相关名称
    # 获取家庭名称
    family_result = await db.execute(select(Family).where(Family.id == family_id))
    family = family_result.scalar_one_or_none()
    family_name = family.name if family else None

    # 获取分类名称
    category_name = None
    if kwargs.get("category_id"):
        category_result = await db.execute(
            select(Category).where(
                Category.id == kwargs["category_id"], Category.family_id == family_id
            )
        )
        category = category_result.scalar_one_or_none()
        category_name = category.name if category else None

    # 获取账户名称
    account_result = await db.execute(
        select(Account).where(Account.id == account_id, Account.family_id == family_id)
    )
    account = account_result.scalar_one_or_none()
    account_name = account.name if account else None

    # 获取目标账户名称(如果是转账)
    to_account_name = None
    if kwargs.get("to_account_id"):
        to_account_result = await db.execute(
            select(Account).where(
                Account.id == kwargs["to_account_id"], Account.family_id == family_id
            )
        )
        to_account = to_account_result.scalar_one_or_none()
        to_account_name = to_account.name if to_account else None

    # 获取项目名称
    project_name = None
    if kwargs.get("project_id"):
        project_result = await db.execute(
            select(Project).where(
                Project.id == kwargs["project_id"], Project.family_id == family_id
            )
        )
        project = project_result.scalar_one_or_none()
        project_name = project.name if project else None

    # 获取成员名称
    member_result = await db.execute(
        select(FamilyMember).where(
            FamilyMember.id == member_id, FamilyMember.family_id == family_id
        )
    )
    member = member_result.scalar_one_or_none()
    member_name = member.full_name if member else None

    db_transaction = Transaction(
        transaction_type=transaction_type,
        amount=amount,
        account_id=account_id,
        account_name=account_name,
        member_id=member_id,
        member_name=member_name,
        family_id=family_id,
        family_name=family_name,
        transaction_date=transaction_date,
        category_name=category_name,
        to_account_name=to_account_name,
        project_name=project_name,
        **kwargs,
    )
    db.add(db_transaction)

    # 更新账户余额
    if account:
        if transaction_type == TransactionType.INCOME:
            account.balance += amount
        elif transaction_type == TransactionType.EXPENSE:
            account.balance -= amount
        # 转账在下面处理

    # 处理转账
    if transaction_type == TransactionType.TRANSFER and kwargs.get("to_account_id"):
        to_account = await get_account_by_id(db, kwargs["to_account_id"], family_id)
        if to_account:
            account.balance -= amount
            to_account.balance += amount

    await db.commit()
    await db.refresh(db_transaction)

    return db_transaction


async def update_transaction(
    db: AsyncSession, transaction_id: int, family_id: int, **kwargs
) -> Transaction | None:
    """更新交易记录"""
    transaction = await get_transaction_by_id(db, transaction_id, family_id)
    if not transaction:
        return None

    # 获取原始数据用于余额调整
    old_amount = transaction.amount
    old_type = transaction.transaction_type
    old_account_id = transaction.account_id
    old_to_account_id = transaction.to_account_id

    # 更新字段
    for key, value in kwargs.items():
        if hasattr(transaction, key) and value is not None:
            setattr(transaction, key, value)

    # 更新账户余额
    if (
        "amount" in kwargs
        or "transaction_type" in kwargs
        or "account_id" in kwargs
        or "to_account_id" in kwargs
    ):
        # 恢复原账户余额
        old_account = await get_account_by_id(db, old_account_id, family_id)
        if old_account:
            if old_type == TransactionType.INCOME:
                old_account.balance -= old_amount
            elif old_type == TransactionType.EXPENSE:
                old_account.balance += old_amount
            elif old_type == TransactionType.TRANSFER and old_to_account_id:
                old_to_account = await get_account_by_id(
                    db, old_to_account_id, family_id
                )
                if old_to_account:
                    old_account.balance += old_amount
                    old_to_account.balance -= old_amount

        # 应用新余额
        new_amount = kwargs.get("amount", old_amount)
        new_type = kwargs.get("transaction_type", old_type)
        new_account_id = kwargs.get("account_id", old_account_id)
        new_to_account_id = kwargs.get("to_account_id", old_to_account_id)

        new_account = await get_account_by_id(db, new_account_id, family_id)
        if new_account:
            if new_type == TransactionType.INCOME:
                new_account.balance += new_amount
            elif new_type == TransactionType.EXPENSE:
                new_account.balance -= new_amount
            elif new_type == TransactionType.TRANSFER and new_to_account_id:
                new_to_account = await get_account_by_id(
                    db, new_to_account_id, family_id
                )
                if new_to_account:
                    new_account.balance -= new_amount
                    new_to_account.balance += new_amount

    await db.commit()
    await db.refresh(transaction)
    return transaction


async def delete_transaction(
    db: AsyncSession, transaction_id: int, family_id: int
) -> bool:
    """删除交易记录(软删除)"""
    transaction = await get_transaction_by_id(db, transaction_id, family_id)
    if not transaction:
        return False

    # 软删除
    transaction.is_deleted = True

    # 恢复账户余额
    account = await get_account_by_id(db, transaction.account_id, family_id)
    if account:
        if transaction.transaction_type == TransactionType.INCOME:
            account.balance -= transaction.amount
        elif transaction.transaction_type == TransactionType.EXPENSE:
            account.balance += transaction.amount
        elif (
            transaction.transaction_type == TransactionType.TRANSFER
            and transaction.to_account_id
        ):
            to_account = await get_account_by_id(
                db, transaction.to_account_id, family_id
            )
            if to_account:
                account.balance += transaction.amount
                to_account.balance -= transaction.amount

    await db.commit()
    return True


async def get_transaction_summary(
    db: AsyncSession,
    family_id: int,
    start_date: date | None = None,
    end_date: date | None = None,
) -> dict[str, Any]:
    """获取交易统计摘要"""
    query = select(Transaction).where(
        Transaction.family_id == family_id, Transaction.is_deleted == False
    )

    if start_date:
        query = query.where(Transaction.transaction_date >= start_date)
    if end_date:
        # Include the entire end_date by setting it to the end of the day
        end_datetime = datetime.combine(end_date, datetime.max.time())
        query = query.where(Transaction.transaction_date <= end_datetime)

    result = await db.execute(query)
    transactions = result.scalars().all()

    total_income = Decimal("0.00")
    total_expense = Decimal("0.00")
    total_transfer = Decimal("0.00")

    for transaction in transactions:
        if transaction.transaction_type == TransactionType.INCOME:
            total_income += transaction.amount
        elif transaction.transaction_type == TransactionType.EXPENSE:
            total_expense += transaction.amount
        elif transaction.transaction_type == TransactionType.TRANSFER:
            total_transfer += transaction.amount

    return {
        "total_income": total_income,
        "total_expense": total_expense,
        "total_transfer": total_transfer,
        "net_income": total_income - total_expense,
        "transaction_count": len(transactions),
    }


async def get_category_statistics(
    db: AsyncSession,
    family_id: int,
    start_date: date | None = None,
    end_date: date | None = None,
) -> list[dict[str, Any]]:
    """获取分类统计"""
    query = (
        select(Transaction, Category)
        .join(Category, Transaction.category_id == Category.id)
        .where(
            Transaction.family_id == family_id,
            Transaction.is_deleted == False,
            Transaction.category_id.isnot(None),
        )
    )

    if start_date:
        query = query.where(Transaction.transaction_date >= start_date)
    if end_date:
        end_datetime = datetime.combine(end_date, datetime.max.time())
        query = query.where(Transaction.transaction_date <= end_datetime)

    result = await db.execute(query)
    results = result.all()

    # 按分类统计
    category_stats = {}
    for transaction, category in results:
        if transaction.category_id:
            if transaction.category_id not in category_stats:
                category_stats[transaction.category_id] = {
                    "category_id": transaction.category_id,
                    "category_name": category.name if category else "Unknown",
                    "total_amount": Decimal("0.00"),
                    "transaction_count": 0,
                }
            category_stats[transaction.category_id]["total_amount"] += (
                transaction.amount
            )
            category_stats[transaction.category_id]["transaction_count"] += 1

    return list(category_stats.values())


async def get_account_statistics(
    db: AsyncSession,
    family_id: int,
    start_date: date | None = None,
    end_date: date | None = None,
) -> list[dict[str, Any]]:
    """获取账户统计"""
    query = select(Transaction).where(
        Transaction.family_id == family_id, Transaction.is_deleted == False
    )

    if start_date:
        query = query.where(Transaction.transaction_date >= start_date)
    if end_date:
        end_datetime = datetime.combine(end_date, datetime.max.time())
        query = query.where(Transaction.transaction_date <= end_datetime)

    result = await db.execute(query)
    transactions = result.scalars().all()

    # 获取账户信息
    accounts_result = await db.execute(
        select(Account).where(Account.family_id == family_id)
    )
    accounts = {account.id: account for account in accounts_result.scalars().all()}

    # 按账户统计
    account_stats = {}
    for transaction in transactions:
        account_id = transaction.account_id
        if account_id not in account_stats:
            account = accounts.get(account_id)
            account_stats[account_id] = {
                "account_id": account_id,
                "account_name": account.name if account else "Unknown",
                "balance": account.balance if account else Decimal("0.00"),
                "income_amount": Decimal("0.00"),
                "expense_amount": Decimal("0.00"),
                "transaction_count": 0,
            }

        if transaction.transaction_type == TransactionType.INCOME:
            account_stats[account_id]["income_amount"] += transaction.amount
        elif transaction.transaction_type == TransactionType.EXPENSE:
            account_stats[account_id]["expense_amount"] += transaction.amount

        account_stats[account_id]["transaction_count"] += 1

    return list(account_stats.values())


async def get_project_statistics(
    db: AsyncSession,
    family_id: int,
    start_date: date | None = None,
    end_date: date | None = None,
) -> list[dict[str, Any]]:
    """获取项目统计"""
    query = (
        select(Transaction, Project)
        .join(Project, Transaction.project_id == Project.id)
        .where(
            Transaction.family_id == family_id,
            Transaction.is_deleted == False,
            Transaction.project_id.isnot(None),
        )
    )

    if start_date:
        query = query.where(Transaction.transaction_date >= start_date)
    if end_date:
        end_datetime = datetime.combine(end_date, datetime.max.time())
        query = query.where(Transaction.transaction_date <= end_datetime)

    result = await db.execute(query)
    results = result.all()

    # 按项目统计
    project_stats = {}
    for transaction, project in results:
        project_id = transaction.project_id
        if project_id not in project_stats:
            project_stats[project_id] = {
                "project_id": project_id,
                "project_name": project.name if project else "Unknown",
                "budget": project.budget if project else None,
                "expense_amount": Decimal("0.00"),
                "transaction_count": 0,
            }

        project_stats[project_id]["expense_amount"] += transaction.amount
        project_stats[project_id]["transaction_count"] += 1

    # 计算剩余预算
    for stats in project_stats.values():
        if stats["budget"] is not None:
            stats["remaining_budget"] = stats["budget"] - stats["expense_amount"]

    return list(project_stats.values())
